
package cn.edu.zhku.citrusmonitor.baseinfo.citrus.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.Bean;
import cn.edu.zhku.citrusmonitor.domain.TbCitrus;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;
import cn.edu.zhku.citrusmonitor.util.ManagerThreadLocal;

public class CitrusDaoImpl implements CitrusDao{
	//GenerousBeanProcessor仅仅重写了父类BeanProcessor的mapColumnsToProperties方法
	BeanProcessor bean = new GenerousBeanProcessor();
	//将GenerousBeanProcessor对象传递给BasicRowProcessor
	RowProcessor processor = new BasicRowProcessor(bean);
	
	@Override
	public int insertCitrus(TbCitrus citrus) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "insert into citrus(citrus_id, citrus_name, description,"
				+ "period, file_path) values(?,?,?,?,?)";
		return qr.update(sql, citrus.getCitrusId(),
				citrus.getCitrusName(), citrus.getDescription(), citrus.getPeriod(), citrus.getFilePath());
	}

	@Override
	public int deleteCitrus(String citrusId) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "delete from citrus where citrus_id=?";
		return qr.update(sql, citrusId);
	}

	@Override
	public String selectFilePathById(String citrusId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select file_path from citrus where citrus_id=?";
		return qr.query(sql, new ScalarHandler<>(1), citrusId);
	}

	@Override
	public List<TbCitrus> selectCitrusList(int pageSize, int currentPage,  Map<String, Object> condition) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		StringBuilder sql = new StringBuilder("select citrus_id, citrus_name,description,period,create_time,file_path from citrus where 1=1");
		List<Object> list = new ArrayList<Object>();
		if(condition == null || condition.size() == 0) {
			sql.append(" limit ?,?");
			list.add((currentPage-1)*pageSize);
			list.add(pageSize);
			return qr.query(sql.toString(), new BeanListHandler<TbCitrus>(TbCitrus.class, processor), list.toArray());
		}else {
			// 遍历添加条件
			for(Map.Entry<String, Object> entry : condition.entrySet()) {
				sql.append(" and " + entry.getKey() + " like ?");
				list.add("%"+entry.getValue()+"%");
			}
			sql.append(" limit ?,?");
			list.add((currentPage-1)*pageSize);
			list.add(pageSize);
			return qr.query(sql.toString(), new BeanListHandler<TbCitrus>(TbCitrus.class, processor), list.toArray());
		}
	}

	@Override
	public int countList(Map<String, Object> condition) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		StringBuilder sql = new StringBuilder("select count(*) from citrus where 1=1");
		List<Object> list = new ArrayList<Object>();
		if(condition == null || condition.size() == 0) {
			long count = qr.query(sql.toString(), new ScalarHandler<>(1));
			return (int)count;
		}else {
			// 遍历添加条件
			for(Map.Entry<String, Object> entry : condition.entrySet()) {
				sql.append(" and " + entry.getKey() + " like ?");
				list.add(entry.getValue());
			}
			long count = qr.query(sql.toString(), new ScalarHandler<>(1), list.toArray());
			return (int)count;
		}
	}

	@Override
	public int updateCitrus(TbCitrus citrus) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "update citrus set citrus_name=?, description=?, period=? where citrus_id=?";
		return qr.update(sql, citrus.getCitrusName(), citrus.getDescription(), citrus.getPeriod(), citrus.getCitrusId());
	}

	@Override
	public TbCitrus selectCitrusById(String citrusId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select citrus_id, technology_id, citrus_name, description, period, file_path, create_time from citrus where citrus_id=?";
		return qr.query(sql, new BeanHandler<TbCitrus>(TbCitrus.class, processor), citrusId);
	}

	@Override
	public int updateCitrusInTnlg(String citrusId, String technologyId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "update citrus set technology_id=? where citrus_id=?";
		return qr.update(sql, technologyId, citrusId);
	}

	@Override
	public List<TbCitrus> selectCitrusesLimit(int size) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select citrus_id, citrus_name, period, file_path, create_time from citrus order by create_time desc limit 0,?";
		return qr.query(sql, new BeanListHandler<TbCitrus>(TbCitrus.class, processor), size);
	}

	@Override
	public List<Bean> selectCitrusList(int pageSize, int currentPage) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select citrus_id as id, citrus_name as title, create_time as time from citrus order by create_time desc limit ?,?";
		return qr.query(sql, new BeanListHandler<Bean>(Bean.class), (currentPage-1)*pageSize, pageSize);
	}

	@Override
	public Bean getCitrusById(String citrusId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select citrus_id as id, citrus_name as title, description as content, file_path as filePath, create_time as time from citrus where citrus_id=?";
		return qr.query(sql, new BeanHandler<Bean>(Bean.class), citrusId);
	}

	@Override
	public int deleteCitrusesByIds(Object [][]params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		int[] s = qr.batch(ManagerThreadLocal.getConnection(), "delete from citrus where citrus_id=?", params);
		return s.length;
	}

}


